package vip.xiaonuo.smzq.modular.nbfb.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.kcl.entity.Zycl;
import vip.xiaonuo.smzq.modular.nbfb.entity.vo.NbfbOneVo;

import java.util.List;

@Mapper
public interface ZyclOneMapper extends BaseMapper<Zycl> {

    /**
     * 年报附表1查询
     *
     * @param nd
     * @author Kevin
     * @since 2024/7/30 21:27
     */
    @Select("-- 修改普通萤石，金银铜；矿产地维度；增加合计\n" +
            "with base as \n" +
            "(\n" +
            "-- 年度，行政区代码，矿区编号，kcmc，jsdxmc，gm，mc，矿产代码，统计对象\n" +
            "select ks.nd, max(ks.xzqdm) xzqdm, ks.kqbh, ks.kcmc, \n" +
            "      case when ks.kcdm in (42201, 42202, 32006, 63701) then '' else ks.jsdxmc end jsdxmc, ks.gm,zh.dm, zh.mc,ks.kcdm, \n" +
            "      case when ks.kcdm in (42201, 42202, 32006, 63701) then 0 else ks.tjdx end tjdx\n" +
            "  from hisi_kshz ks\n" +
            "  left join hisi_ks_zyclhz zy on ks.nd = zy.nd and ks.kqbh = zy.kqbh and ks.djflbh = zy.djflbh and ks.kcdm = zy.kcdm and ks.tjdx = zy.tjdx\n" +
            "  left join kcl_cb06_kc kc on ks.kcdm=kc.kcdm and ks.tjdx = kc.tjdx\n" +
            "  left join kcl_cb01_zh zh on kc.kclb::int8/10*100 = zh.dm::int8\n" +
            "  where  ks.sbbsm<>3 and zy.zycllx IN ('TM', 'KZ', 'TD')  and ks.nd =#{nd} \n" +
            "  group by ks.nd, ks.kqbh, ks.kcmc, ks.jsdxmc, ks.gm, zh.dm, zh.mc, ks.kcdm, ks.tjdx\n" +
            "),\n" +
            "\n" +
            "-- 原始数据\n" +
            "data as (\n" +
            "select  CASE \n" +
            "            WHEN mc IS NULL OR mc = '' THEN '700400' \n" +
            "            ELSE dm \n" +
            "        END AS dm, \n" +
            "        CASE \n" +
            "            WHEN mc IS NULL OR mc = '' THEN '贵金属、稀有分散元素矿产' \n" +
            "            ELSE mc \n" +
            "        END AS mc, \n" +
            "\t\t\t\t kcdm, kcmc, tjdx, jsdxmc, \n" +
            "    sum(case when gm ='大型' then 1 else 0 end) dx, \n" +
            "    sum(case when gm ='中型' then 1 else 0 end) zx, \n" +
            "    sum(case when gm ='小型' then 1 else 0 end) xx,\n" +
            "    count (kqbh) zs\n" +
            "     from base\n" +
            "group by dm, mc,  kcdm, kcmc, tjdx, jsdxmc\n" +
            "-- order by kcdm, tjdx\n" +
            "),\n" +
            "-- 小计\n" +
            "xj as (\n" +
            "select dm, mc, 99999 kcdm,'小计' kcmc, 0 tjdx, '' jsdxmc, sum(dx) dx, sum(zx) zx, sum(xx) xx, sum(zs) zs from data \n" +
            "group by dm, mc\n" +
            "),\n" +
            "-- 总计\n" +
            "zj as (\n" +
            "select 9999999 dm, ''mc, 199999 kcdm,'总计' kcmc, 0 tjdx, '' jsdxmc, sum(dx) dx, sum(zx) zx, sum(xx) xx, sum(zs) zs from data \n" +
            ")\n" +
            "\n" +
            "select mc as dlmc,(case when jsdxmc <> '' then CONCAT(kcmc, ' (', jsdxmc, ')') else kcmc end) as kcmc, dx as big, zx as middle, xx as small, zs as total from (\n" +
            "select * from data\n" +
            "union\n" +
            "select * from xj\n" +
            "union\n" +
            "select * from zj\n" +
            ") T\n" +
            "order by dm, kcdm, tjdx")
    List<NbfbOneVo> getNbfbOneListNew(Integer nd);



}
